{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import Table, Column, Integer, Numeric, String, Boolean\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "class Cookie(Base):\n",
    "    __tablename__ = 'cookies'\n",
    "\n",
    "    cookie_id = Column(Integer(), primary_key=True)\n",
    "    cookie_name = Column(String(50), index=True)\n",
    "    cookie_recipe_url = Column(String(255))\n",
    "    cookie_sku = Column(String(55))\n",
    "    quantity = Column(Integer())\n",
    "    unit_cost = Column(Numeric(12, 2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('cookies', MetaData(bind=None), Column('cookie_id', Integer(), table=<cookies>, primary_key=True, nullable=False), Column('cookie_name', String(length=50), table=<cookies>), Column('cookie_recipe_url', String(length=255), table=<cookies>), Column('cookie_sku', String(length=55), table=<cookies>), Column('quantity', Integer(), table=<cookies>), Column('unit_cost', Numeric(precision=12, scale=2), table=<cookies>), schema=None)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Cookie.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "from sqlalchemy import DateTime\n",
    "\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    \n",
    "    user_id = Column(Integer(), primary_key=True)\n",
    "    username = Column(String(15), nullable=False, unique=True)\n",
    "    email_address = Column(String(255), nullable=False)\n",
    "    phone = Column(String(20), nullable=False)\n",
    "    password = Column(String(25), nullable=False)\n",
    "    created_on = Column(DateTime(), default=datetime.now)\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('users', MetaData(bind=None), Column('user_id', Integer(), table=<users>, primary_key=True, nullable=False), Column('username', String(length=15), table=<users>, nullable=False), Column('email_address', String(length=255), table=<users>, nullable=False), Column('phone', String(length=20), table=<users>, nullable=False), Column('password', String(length=25), table=<users>, nullable=False), Column('created_on', DateTime(), table=<users>, default=ColumnDefault(<function <lambda> at 0x1124c30c8>)), Column('updated_on', DateTime(), table=<users>, onupdate=ColumnDefault(<function <lambda> at 0x112488d70>), default=ColumnDefault(<function <lambda> at 0x112488de8>)), schema=None)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "User.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import ForeignKey\n",
    "from sqlalchemy.orm import relationship, backref\n",
    "\n",
    "class Order(Base):\n",
    "    __tablename__ = 'orders'\n",
    "    order_id = Column(Integer(), primary_key=True)\n",
    "    user_id = Column(Integer(), ForeignKey('users.user_id'))\n",
    "    shipped = Column(Boolean(), default=False)\n",
    "    user =  relationship(\"User\", backref=backref('orders', order_by=id))\n",
    "\n",
    "\n",
    "class LineItems(Base):\n",
    "    __tablename__ = 'line_items'\n",
    "    line_items_id = Column(Integer(), primary_key=True)\n",
    "    order_id = Column(Integer(), ForeignKey('orders.order_id'))\n",
    "    cookie_id = Column(Integer(), ForeignKey('cookies.cookie_id'))\n",
    "    quantity = Column(Integer())\n",
    "    extended_cost = Column(Numeric(12, 2))\n",
    "    order = relationship(\"Order\", backref=backref('line_items', order_by=line_items_id))\n",
    "    cookie = relationship(\"Cookie\", uselist=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('sqlite:///:memory:')\n",
    "\n",
    "Base.metadata.create_all(engine)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
